//201902104011王雪梦
package bysj.dao;
import bysj.domain.Teacher;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class TeacherDao {
    private static TeacherDao teacherDao = null;

    private TeacherDao() {
    }

    public static TeacherDao getInstance() {
        if(teacherDao==null){
            teacherDao = new TeacherDao();
        }
        return teacherDao;
    }

    public Collection<Teacher> findAll(Connection connection, List<Condition>conditionList, Pagination pagination) throws SQLException {
        Collection<Teacher> desiredTeachers = new HashSet<Teacher>();
		StringBuilder sql_select = new StringBuilder("SELECT * FROM teacher");
        int totalNum = CommonDao.getInstance().count(connection,"teacher");
		if(conditionList!=null){
		    String whereClause = Condition.toWhereClause(conditionList);
		    sql_select.append(whereClause);
        }
        if(pagination!=null){
            String limit= pagination.toLimitClause(totalNum)+" ";
            sql_select.append(limit);
        }
        System.out.println(sql_select.toString());
        //获得连接对象
        PreparedStatement preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行SQL查询语句并获得结果集对象
        ResultSet resultSet = preparedStatement.executeQuery();
        while(resultSet.next()){
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            Teacher desiredTeacher = new Teacher(name, no);
            desiredTeacher.setId(id);
            desiredTeachers.add(desiredTeacher);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet,preparedStatement);
        return desiredTeachers;
    }

    public Teacher find(int id,Connection connection) throws SQLException {
        Teacher teacher =null;
        //创建sql语句
        String teacher_sql = "SELECT * FROM teacher WHERE id = ?";
        //创建预编译对象
        PreparedStatement pstmt= connection.prepareStatement(teacher_sql);
        //为预编译对象赋值
        pstmt.setInt(1,id);
        //获得结果集对象
        ResultSet resultSet = pstmt.executeQuery();
        if (resultSet.next()){
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            teacher = new Teacher(name, no);
            teacher.setId(id);
            System.out.println(teacher);
        }
        //关闭三个资源
        JdbcHelper.close(resultSet, pstmt);
        return teacher;
    }

    public boolean update(Teacher teacher,Connection connection) throws SQLException{
        String teacher_sql = "update teacher set name=?,no=? where id="+teacher.getId();
        PreparedStatement preparedStatement = connection.prepareStatement(teacher_sql);
       // preparedStatement.setInt(1,teacher.getUser().getId());
        preparedStatement.setString(1,teacher.getName());
        preparedStatement.setString(2,teacher.getNo());
        int affectedRowNum = preparedStatement.executeUpdate();
        return affectedRowNum>0;
    }

    public void add(Teacher teacher,Connection connection) throws SQLException{
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement = connection.prepareStatement("insert into teacher"+"(id,name,no,profTitle_id,degree_id,department_id,actor_id)"+"values (?,?,?,?,?,?,?)", Statement.RETURN_GENERATED_KEYS);//要求返回主键集合
        //为预编译对象赋值
        preparedStatement.setInt(1,teacher.getId());
        preparedStatement.setString(2,teacher.getName());
        preparedStatement.setString(3,teacher.getNo());
        preparedStatement.setInt(4,teacher.getTitle().getId());
        preparedStatement.setInt(5,teacher.getDegree().getId());
        preparedStatement.setInt(6,teacher.getDepartment().getId());
        preparedStatement.setInt(7,teacher.getId());
        //执行预编译语句，用其返回值，影响的行数为赋值affectedRowNum
        preparedStatement.executeUpdate();
        JdbcHelper.close(preparedStatement);
        //本方法是事务的一部分，不能释放资源
        //新增元组的i
    }
    public boolean delete(Integer id,Connection connection) throws SQLException {
        //创建sql语句
        String department_sql = "delete from teacher where id=?";
        //在该连接创建预编译语句对象
        PreparedStatement pstmt = connection.prepareStatement(department_sql);
        //为预编译参数赋值
        pstmt.setInt(1, id);
        //执行预编译对象的executeUpdate方法，获得删除的记录行数
        int affectedRowNum = pstmt.executeUpdate();
        //关闭两个资源
        JdbcHelper.close(pstmt);
        return affectedRowNum > 0;
    }

}
